import requests
from bs4 import BeautifulSoup
import sqlite3

# 假设的期刊文章 URL
articles_urls = [
    'http://tougao.xsbja.com/index.php?s=&c=content&a=issue&id=4531&key=7',
    'http://tougao.xsbja.com/index.php?s=&c=content&a=issue&id=4531&key=8'
]

# SQLite 数据库连接
conn = sqlite3.connect('journal_articles.db')
c = conn.cursor()

# 创建表
c.execute('''CREATE TABLE IF NOT EXISTS articles        
             (id INTEGER PRIMARY KEY AUTOINCREMENT,        
              title TEXT NOT NULL,        
              author TEXT NOT NULL,        
              abstract TEXT NOT NULL)''')


def insert_article(cursor, title, author, abstract):
    if title and author and abstract:
        cursor.execute("INSERT INTO articles (title, author, abstract) VALUES (?, ?, ?)",
                       (title, author, abstract))
        print(f"Inserted article: {title}")
    else:
        print("Incomplete article data.")


for url in articles_urls:
    # 发送HTTP请求
    response = requests.get(url)
    # 检查请求是否成功
    if response.status_code == 200:
        # 解析HTML内容
        soup = BeautifulSoup(response.text, 'html.parser')

        # 提取文章信息
        title = None
        author = None
        abstract = None

        # 假设HTML结构
        title_tag = soup.find('div', class_='wzbx')  # 根据实际HTML结构调整
        if title_tag:
            title = title_tag.get_text(strip=True)
            print(f"标题: {title}")

        abstract_p = soup.find('div', class_='wzzy')  # 假设的类名
        if abstract_p:
            abstract = abstract_p.get_text(strip=True)
            print(f"{abstract}")

        author_div = soup.find('div', class_='some-class-that-contains-author')  # 根据实际HTML结构调整
        if author_div:
            author_text = author_div.find(
                text=lambda text: text and isinstance(text, str) and text.strip().startswith('作者：'))
            if author_text:
                author = author_text.strip().replace('作者：', '').strip()
                print("作者:", author)
            else:
                author = ''
        else:
            author = ''

            # 插入数据库
        insert_article(c, title, author, abstract)

    # 在所有数据插入后提交事务
conn.commit()

# 关闭数据库连接
conn.close()